package com.motong.gongdan.util;

import cn.hutool.core.io.IoUtil;
import com.motong.gongdan.vo.system.ExcelTemplate.vo.CellDataVo;
import com.motong.gongdan.vo.system.ExcelTemplate.vo.ExcelTemplateDataVo;
import lombok.Data;
import lombok.experimental.Accessors;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.CollectionUtils;

import java.io.InputStream;
import java.io.OutputStream;
import java.net.URL;
import java.util.*;
import java.util.concurrent.atomic.AtomicBoolean;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

@Slf4j
@Data
@Accessors(chain = true)
public class ExcelTemplateUtil {

    // 名称字段映射
    private Map<String, String> cnEnMap = Collections.emptyMap();

    public ExcelTemplateUtil(Map<String, String> cnEnMap){
        this.cnEnMap = cnEnMap;
    }

    public ExcelTemplateUtil(){
    }

    public void exportExcelByTemplate(InputStream inputStream,List<ExcelTemplateDataVo> excelData, OutputStream outputStream) {
        try (
             Workbook workbook = WorkbookFactory.create(inputStream);) {
            int oriNumberOfSheets = workbook.getNumberOfSheets();
            if (oriNumberOfSheets > 1) {
                workbook.removeSheetAt(0);
            }
            Sheet mainSheet = workbook.createSheet();
            for (int count = 0; count < excelData.size(); count++) {
                Map<String, Object> singleData = excelData.get(count).getSingleData();
                List<Map<String, Object>> listData = excelData.get(count).getListData();
                int tableOffset = 4;
                AtomicInteger rowOffset = new AtomicInteger(count == 0 ? 0 : mainSheet.getLastRowNum() + tableOffset);
                Sheet templateSheet = workbook.getSheetAt(0);
                List<CellRangeAddress> tempNewSheetMerged = templateSheet.getMergedRegions().stream().peek(i -> {
                    i.setFirstRow(rowOffset.get() + i.getFirstRow());
                    i.setLastRow(rowOffset.get() + i.getLastRow());
                }).collect(Collectors.toList());
                Iterator<Row> rowIterator = templateSheet.rowIterator();
                while (rowIterator.hasNext()) {
                    Row sourceRow = rowIterator.next();
                    Row targetRow = mainSheet.createRow(sourceRow.getRowNum() + rowOffset.get());
                    this.copyRowStyle(sourceRow, targetRow);
                    List<List<CellDataVo>> loopData = new ArrayList<>();
                    Iterator<Cell> cellIterator = sourceRow.cellIterator();
                    while (cellIterator.hasNext()) {
                        Cell sourceCell = cellIterator.next();
                        Cell targetCell = targetRow.createCell(sourceCell.getColumnIndex());
                        this.copyCellStyle(sourceCell, targetCell);
                        mainSheet.setColumnWidth(targetCell.getColumnIndex(), templateSheet.getColumnWidth(targetCell.getColumnIndex()));
                        String templateCellValue = sourceCell.getStringCellValue();
                        Object newCellValue = templateCellValue;
                        Matcher matcher = Pattern.compile("(?<=\\{)(.+?)(?=})").matcher(templateCellValue);
                        while (matcher.find()) {
                            String patchName = matcher.group();
                            if (".".equals(patchName.substring(0,1))) {
                                newCellValue = "";
                                String propertyName = patchName.substring(1);
                                for (int i = 0; i < listData.size(); i++) {
                                    Map<String, Object> data = listData.get(i);
                                    Object dataValue = data.get(cnEnMap.getOrDefault(propertyName,propertyName));
                                    boolean isExist = (loopData.size() - 1) >= i && Objects.nonNull(loopData.get(i));
                                    List<CellDataVo> cellValue = isExist ? loopData.get(i) : new ArrayList<>();
                                    if (Objects.nonNull(dataValue)) {
                                        cellValue.add(new CellDataVo().setCellIndex(sourceCell.getColumnIndex()).setValue(dataValue));
                                    }
                                    if (!isExist) {
                                        loopData.add(i, cellValue);
                                    }
                                }
                            } else {
                                Object tempNewValue = singleData.getOrDefault(cnEnMap.getOrDefault(patchName, patchName), null);
                                if (tempNewValue instanceof URL) {
                                    this.importPic(workbook, targetCell, tempNewSheetMerged, mainSheet, (URL) tempNewValue);
                                    newCellValue = null;
                                } else {
                                    newCellValue = Objects.isNull(tempNewValue) ? "" : tempNewValue;
                                }
                            }
                        }
                        if (Objects.nonNull(newCellValue)) {
                            targetCell.setCellValue(newCellValue.toString());
                        }
                    }
                    if (!CollectionUtils.isEmpty(loopData)) {
                        AtomicBoolean isFirst = new AtomicBoolean(true);
                        loopData.forEach(i -> {
                            if (!CollectionUtils.isEmpty(i)) {
                                Row listNewRow = mainSheet.createRow(sourceRow.getRowNum() + rowOffset.getAndIncrement());
                                if (!isFirst.get()) {
                                    tempNewSheetMerged.forEach(n -> {
                                        if (n.getFirstRow() >= listNewRow.getRowNum()) {
                                            n.setFirstRow(n.getFirstRow() + 1);
                                            n.setLastRow(n.getLastRow() + 1);
                                        }
                                    });
                                }
                                isFirst.set(false);
                                this.copyRowStyle(sourceRow, listNewRow);
                                i.forEach(n -> {
                                    Cell newCell = listNewRow.createCell(n.getCellIndex());
                                    if (n.getValue() instanceof URL) {
                                        this.importPic(workbook, newCell, tempNewSheetMerged, mainSheet, (URL) n.getValue());
                                    } else {
                                        newCell.setCellValue(n.getValue().toString());
                                    }

                                });
                            }
                        });
                        rowOffset.getAndDecrement();
                    }
                }
                tempNewSheetMerged.forEach(mainSheet::addMergedRegion);
            }
            // 去除模板
            workbook.removeSheetAt(0);
            workbook.write(outputStream);
        } catch (Exception e) {
            log.error("excel模板导出发生异常");
        }
    }

    // 复制行格式
    private void copyRowStyle(Row sourceRow, Row targetRow) {
        targetRow.setRowStyle(sourceRow.getRowStyle());
        targetRow.setHeight(sourceRow.getHeight());
        targetRow.setHeightInPoints(sourceRow.getHeightInPoints());
        targetRow.setZeroHeight(sourceRow.getZeroHeight());
    }

    // 复制单元格格式
    private void copyCellStyle(Cell sourceCell, Cell targetCell) {
        targetCell.setCellComment(sourceCell.getCellComment());
        targetCell.setHyperlink(sourceCell.getHyperlink());
        targetCell.setCellStyle(sourceCell.getCellStyle());
    }


    /**
     * 根据单元格格式导入图片
     *
     * @param workbook    工作簿
     * @param mergedStyle 合并样式
     * @param sheet       sheet
     * @param picValue    图片计算
     */
    private void importPic(Workbook workbook, Cell targetCell, List<CellRangeAddress> mergedStyle, Sheet sheet, URL picValue) {
        int startRowIndex = targetCell.getRowIndex();
        int startColumnIndex = targetCell.getColumnIndex();
        XSSFDrawing patriarch = (XSSFDrawing) sheet.createDrawingPatriarch();
        XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 1023, 255, (short) startColumnIndex, startRowIndex, (short) startColumnIndex + 1, startRowIndex + 1);
        for (CellRangeAddress i : mergedStyle) {
            if (i.getFirstRow() == startRowIndex && i.getFirstColumn() == startColumnIndex) {
                anchor = new XSSFClientAnchor(0, 0, 1023, 255, (short) startColumnIndex, startRowIndex, (short) i.getLastColumn() + 1, i.getLastRow() + 1);
            }
        }
        byte[] picBytes = null;
        try (InputStream picInputStream = picValue.openStream();) {
            picBytes = IoUtil.readBytes(picInputStream);
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (Objects.nonNull(picBytes)) {
            patriarch.createPicture(anchor, workbook.addPicture(picBytes, XSSFWorkbook.PICTURE_TYPE_JPEG));
        }
    }


}
